﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MakeIT_Bill_Maker
{
    public partial class frmReading : Form
    {
        private Database.DatabaseConnection conn = new Database.DatabaseConnection();
        string sql;
        DataSet dataset;
        DataTable dt;
        int nextApt = 0;

        public frmReading()
        {
            InitializeComponent();
        }

        private void btnConfirm_Click(object sender, EventArgs e)
        {
            string aptNo = cmbAptNo.Text;
            string prevdate = dtppredate.Value.Date.ToString("MM/dd/yyyy");
            string prevread = txtpreread.Text;
            string currdate = dtpcurdate.Value.Date.ToString("MM/dd/yyyy");
            string currread = txtcurread.Text;
            string unit = txtunits.Text;
            string charge = txtcharge.Text;
            string arrears = txtarrears.Text;
            string total = txttotal.Text;
            string service = txtservice.Text;
            string totaldue = txttotalamount.Text;
            string welfare = txtWelfare.Text;
            string duedate = dtpDueDate.Value.Date.ToString("MM/dd/yyyy");
            string billNo = txtbillno.Text;

            if (aptNo == "")
            {
                MessageBox.Show("Please enter the apartment number.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (currread == "")
            {
                MessageBox.Show("Please enter the current reading.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (int.Parse(currread) < int.Parse(prevread))
            {
                MessageBox.Show("Current reading should larger than the previous reading.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (Convert.ToDateTime(prevdate).Month == Convert.ToDateTime(currdate).Month)
            {
                MessageBox.Show("Can't have two readings in the same month.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (int.Parse(currread) < 0 || int.Parse(prevread) < 0)
            {
                MessageBox.Show("Please enter positive values for the readings.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                string sql4 = "INSERT INTO UsersMonthly  (`BillNo`, `PrevReadDate`, `PrevRead`, `CurrReadDate`,`CurrRead`,`UnitsConsumed`,`Charge`,`Arrears`,`TotalAmount`,`FinalDateOfPayment`,`IsPaid`,`Service`,`Total`,`welfare`) VALUES "
                            + "('" + aptNo + "','"
                            + prevdate + "','"
                            + prevread + "','"
                            + currdate + "','"
                            + currread + "','"
                            + unit + "','"
                            + charge + "','"
                            + arrears + "','"
                            + totaldue + "','"
                            + duedate + "','"
                            + 0 + "','"
                            + service + "','"
                            + total + "','"
                            + welfare + "')";

                Boolean b = conn.add(sql4);

                string sqlTot = "UPDATE Users SET Arrears = '" + totaldue + "', MonthlyArrears = '" + total + "' WHERE AptNo='" + aptNo + "'";
                Boolean c = conn.add(sqlTot);
                
                if (b == true)
                {
                    if (nextApt == 271)
                    {
                        nextApt = 0;
                        MessageBox.Show("Inserting for this month is finished.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        nextApt += 1;
                    }

                    string aptNo1 = dt.Rows[nextApt]["AptNo"].ToString();
                    cmbAptNo.Text = aptNo1;
                                  
                    txtcurread.Text ="";
                    txtunits.Text ="";
                    txtcharge.Text ="";
                    txttotal.Text ="";
                    txttotalamount.Text ="";
                    txtbillno.Text = (int.Parse(billNo) + 1).ToString();
                    
                    // BillNo Update
                    sql4 = "UPDATE OtherFixed SET BillNo = '" + billNo + "' WHERE Index=1";
                    b = conn.edit(sql4);

                    // NextApt Update
                    sql4 = "UPDATE OtherFixed SET NextApt = '" + nextApt + "' WHERE Index=1";
                    b = conn.edit(sql4);

                    btnConfirm.Enabled = false;
                    lblWarning.Text = "";
                }
                else
                {
                    MessageBox.Show("Failed to add new Record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void frmReading_Load(object sender, EventArgs e)
        {
            sql = "SELECT * FROM Users";
            dataset = conn.Show(sql);
            dt = dataset.Tables[0];

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count && i < 272; i++)
                {
                    cmbAptNo.Items.Add(dt.Rows[i]["AptNo"].ToString());
                }
            }

            // Global Bill No
            string sql2 = "SELECT BillNo FROM OtherFixed WHERE Index=1";
            DataSet dataset2 = conn.Show(sql2);
            DataTable dt2 = dataset2.Tables[0];
            txtbillno.Text = ((int.Parse(dt2.Rows[0]["BillNo"].ToString())) + 1).ToString();

            // Show the next apartment to calculate the bill
            sql2 = "SELECT NextApt FROM OtherFixed WHERE Index=1";
            dataset2 = conn.Show(sql2);
            dt2 = dataset2.Tables[0];
            nextApt = int.Parse(dt2.Rows[0]["NextApt"].ToString());
            cmbAptNo.Text = dt.Rows[nextApt]["AptNo"].ToString();
        }

        private void cmbAptNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sql3 = "SELECT TOP 1 * FROM Users u LEFT JOIN  UsersMonthly m ON m.BillNo = u.AptNo WHERE  u.AptNo='" + cmbAptNo.Text.ToString() + "' ORDER BY PrevReadDate DESC"; //ASC
            DataSet dataset3 = conn.Show(sql3);
            DataTable dt3 = dataset3.Tables[0];
            if (dt3.Rows.Count > 0)
            {
                txtarrears.Text = dt3.Rows[0]["u.Arrears"].ToString();
                txtPenalty.Text = dt3.Rows[0]["Penalty"].ToString();
                txtName.Text = dt3.Rows[0]["Name"].ToString();
                txtMeter.Text = dt3.Rows[0]["MeterNo"].ToString();
                txtpreread.Enabled = true;
                dtppredate.Enabled = true;
                if (!dt3.Rows[0]["CurrReadDate"].ToString().Equals(""))
                {
                    txtpreread.Text = dt3.Rows[0]["CurrRead"].ToString();
                    txtpreread.Enabled = false;

                    dtppredate.Value = Convert.ToDateTime(dt3.Rows[0]["CurrReadDate"]);
                    dtppredate.Enabled = false;
                    txtcurread.Focus();
                }
                else
                {
                    txtpreread.Text = "";
                    txtpreread.Focus();
                }
            }
        }

        private void txtcurread_Validated(object sender, EventArgs e)
        {
            //txtservice.Text = Calculation.GetServiceCharge(int.Parse(txtunits.Text)).ToString();
            txtWelfare.Text = Calculation.GetWelfare().ToString();
            if (txtcurread.Text != "")
            {
                txtunits.Text = Calculation.GetUnitsConsumed(int.Parse(txtpreread.Text), int.Parse(txtcurread.Text)).ToString();
                txtcharge.Text = Calculation.GetCharge(int.Parse(txtunits.Text)).ToString();
                txtservice.Text = Calculation.GetServiceCharge(int.Parse(txtunits.Text)).ToString();
                txttotal.Text = (Calculation.GetCharge(int.Parse(txtunits.Text)) + Calculation.GetServiceCharge(int.Parse(txtunits.Text)) + Calculation.GetWelfare()).ToString();
                
                if (txtarrears.Text == "")
                {
                    txtarrears.Text = "0";
                }
                
                txttotalamount.Text = (int.Parse(txttotal.Text) + int.Parse(txtarrears.Text) + int.Parse(txtPenalty.Text)).ToString();
            }
            else
            {
                MessageBox.Show("Please enter the 'Current Reading'", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void txtcurread_TextChanged(object sender, EventArgs e)
        {

        }

        private void cmbAptNo_SelectedIndexChanged_1(object sender, EventArgs e)
        {

        }

        // Close Button
        private void btnClose_Click(object sender, EventArgs e)
        {
            /*int billNo = int.Parse(txtbillno.Text);
            string sql = "UPDATE OtherFixed SET BillNo = '" + billNo + "' WHERE Index=1";
            bool b = conn.edit(sql);
            this.Close();*/
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            lblWarning.Text = "Check details again and press the button Confirm...";
            btnConfirm.Enabled = true;
        }
    }
}
